package com.cn.jdia.springmvc.handlers;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.cn.jdia.attence.utils.CharsetUtils;
import com.cn.jdia.attence.utils.DateUtils;
import com.cn.jdia.attence.utils.WordsUtils;
import com.google.gson.Gson;

@Controller
public class RotaFileController {

	@RequestMapping("/uploadrotafile")
	@ResponseBody
	public String uploadRotaFile(HttpServletRequest request, HttpServletResponse response,
			@RequestParam("csvfile") MultipartFile csvfile) {
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			// System.out.println(csvfile.getOriginalFilename());

			String oldFileName = csvfile.getOriginalFilename();
			String realPath = request.getServletContext().getRealPath(".");
			System.out.println(realPath);

			String saveFilePath = realPath.replaceAll("\\\\", "\\\\\\\\") + "\\WEB-INF\\upload";
			// System.out.println(saveFilePath);

			if (csvfile != null && oldFileName != null && oldFileName.length() > 0) {
				String newFileName = oldFileName;
				File newFile = new File(saveFilePath + "\\" + newFileName);
				// 将内存中的数据写入磁盘
				csvfile.transferTo(newFile);
				// 将新名称返回到前端
				map.put("success", "成功");
				map.put("url", newFileName);
			} else {
				map.put("error", "不合法");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		Gson gson = new Gson();
		return gson.toJson(map);
	}

	@RequestMapping("/entryrota")
	@ResponseBody
	public String entryRotaFile(String month, HttpServletRequest request, HttpServletResponse response) {
		System.out.println("month:" + month);

		String realPath = request.getServletContext().getRealPath(".");
		String filePath = realPath.replaceAll("\\\\", "\\\\\\\\") + "\\\\WEB-INF\\\\upload\\\\" + month + ".csv";

		System.out.println(filePath);

		File file = new File(filePath);
		BufferedReader reader = null;

		if (!file.exists()) {
			return "-1";
		}

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource");

		String code = CharsetUtils.getFileEncode(filePath);
		try {
			if ("asci".equals(code)) {
				code = "GBK";
			}
			reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), code));

			String tempString = null;
			int line = 1;
			String name = null;

			String sql = null;
			String janDate = null;

			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();

			String del = "delete from jan_rota_contrast where rota_date like '" + month.replaceAll("-", "") + "%'";
			st.executeUpdate(del);

			// jan_rota
			while ((tempString = reader.readLine()) != null) {
				// 处理
				String[] split = tempString.split(",");
				String dept = split[0].trim();
				// System.out.println(dept + " : " + split.length);

				// not dangyue
				DateTimeFormatter format = DateTimeFormat.forPattern("yyyy-MM");
				DateTime dateTime = DateTime.parse(month, format);   
				
//				DateTime dateTime = new DateTime();
				int len = dateTime.dayOfMonth().getMaximumValue();

				// System.out.println(len);
				// int len = (split.length > 31) ? 31 : split.length;
				System.out.println(len);

				for (int i = 1; i <= len; i++) {
					name = WordsUtils.trimMiddle(split[i]);
					if ("".equals(name)) {
						continue;
					}

					janDate = month.replaceAll("-", "") + String.format("%02d", i);
					sql = "insert into jan_rota_contrast (rota_date,rota_person,person_dept,real_person) values('"
							+ janDate + "','" + name + "','" + dept
							+ "',(select person_name from jan_attence where turnover=1 and check_date='" + janDate
							+ "' and person_dept='" + dept + "'  LIMIT 1))";

					// System.out.println(sql);
					int ret = st.executeUpdate(sql);
				}
				System.out.println();
				line++;
			}
			reader.close();
			st.close();
			conn.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e2) {
			e2.printStackTrace();
		} finally {
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
			}
		}
		return "0";
	}

	@RequestMapping("/analysisrota")
	@ResponseBody
	public String analysisRota(String month, HttpServletRequest request, HttpServletResponse response) {

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource");

		try {
			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();

			String update = "UPDATE jan_rota_contrast SET is_accordant=FALSE where rota_date like '"
					+ month.replaceAll("-", "") + "%' and  rota_person!=real_person";
			int ret = st.executeUpdate(update);

			String xz = "UPDATE jan_rota_contrast SET is_accordant=TRUE where rota_date like '"
					+ month.replaceAll("-", "") + "%' and  rota_person='刘军' and real_person='刘军凯亚'";
			ret = st.executeUpdate(xz);

			// 关联SMS调换班
			String check = "select id,rota_date,person_dept,rota_person,real_person from jan_rota_contrast where rota_date like '"
					+ month.replaceAll("-", "") + "%' and is_accordant=false";
			ResultSet qrs = st.executeQuery(check);

			int rota_id = 0;
			String attTime = null;
			String rota_person = null;
			String real_person = null;
			String person_dept = null;

			List<Map<String, String>> list = new ArrayList<>();
			while (qrs.next()) {
				rota_id = qrs.getInt("id");
				attTime = DateUtils.format(qrs.getString("rota_date"));
				rota_person = qrs.getString("rota_person");
				real_person = qrs.getString("real_person");
				person_dept = qrs.getString("person_dept");

				Map<String, String> map = new HashMap<>();
				map.put("id", rota_id + "");
				map.put("time", attTime);
				map.put("rota", rota_person);
				map.put("real", real_person);
				map.put("dept", person_dept);
				list.add(map);
			}
			qrs.close();

			System.out.println(list.size());

			// ergodic list
			String query = null;
			for (Map<String, String> map : list) {
				if ((map.get("dept").equals("凯亚公司")) && (map.get("rota").equals("刘军"))) {
					map.put("rota", "刘军凯亚");
				}

				if ((map.get("dept").equals("凯亚公司")) && (map.get("real").equals("刘军"))) {
					map.put("real", "刘军凯亚");
				}

				query = "select remark from tm_sms_attendance_agent where attendance_time='" + map.get("time")
						+ "' and (create_id_card= (select identity_id from jan_person_cast where name='"
						+ map.get("rota")
						+ "')  or create_id_card= (select indentity_id_sms from jan_person_cast where name='"
						+ map.get("rota")
						+ "')) and (agent_id_card=(select identity_id from jan_person_cast where name='"
						+ map.get("real")
						+ "') or agent_id_card=(select indentity_id_sms from jan_person_cast where name='"
						+ map.get("real") + "'))";
				// System.out.println(query);

				ResultSet cqrs = st.executeQuery(query);
				int count = 0;
				String reason = null;
				while (cqrs.next()) {
					count++;
					reason = WordsUtils.trimMiddle(cqrs.getString("remark"));
				}
				cqrs.close();

				if (count != 0) {
					// System.out.println(count + "," + map.get("id") + "," +
					// reason);
					// update
					String update_agent = "update jan_rota_contrast set reason='" + reason + "' where id="
							+ map.get("id");
					System.out.println(update_agent);
					int executeUpdate = st.executeUpdate(update_agent);
					System.out.println(executeUpdate);
				}
			}

			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return "0";
	}
}
